﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using OgilvyOne.Borad.IDAL;

namespace OgilvyOne.Borad.DAL
{
    public class QuestionsDAL : IDALQuestions
    {
        private const string ADD_SQL = "INSERT INTO questions(number,options,flag,email,name,mobile) VALUES(@number,@options,@flag,@email,@name,@mobile);select @@IDENTITY";
        public int Add(string number, string options, string flag, string email, string name, string mobile)
        {
            int id = 0;
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@number",SqlDbType.NVarChar),
                new SqlParameter("@options",SqlDbType.NVarChar),
                new SqlParameter("@flag",SqlDbType.Text),
                new SqlParameter("@email",SqlDbType.NVarChar),
                new SqlParameter("@name",SqlDbType.NVarChar),
                new SqlParameter("@mobile",SqlDbType.NVarChar)
            };
            parms[0].Value = number;
            parms[1].Value = options;
            parms[2].Value = flag;
            parms[3].Value = email;
            parms[4].Value = name;
            parms[5].Value = mobile;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string s = SQLHelper.ExecuteScalar(trans, CommandType.Text, ADD_SQL, parms).ToString();
                        id = int.Parse(s);
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return id;
        }

        public int IsExist(string email)
        {
            string sql = string.Format("select count(email) from questions where email='{0}'", email);
            int res=0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        object obj = SQLHelper.ExecuteScalar(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, null);
                        res = Convert.ToInt32(obj);
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return res;
        }
       
    }
}
